import sqlite3
from datetime import datetime, timedelta
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
# 设置matplotlib支持中文显示
plt.rcParams['font.sans-serif'] = ['SimHei', 'Arial Unicode MS', 'DejaVu Sans']
plt.rcParams['axes.unicode_minus'] = False


def tongji_jz_hours(jz_name, startTimestamp, endTimestamp, period='daily'):
    '''
    统计时间区间内指定机组运行的小时数
    :param jz_name: 机组名称，如'JZ1', 'JZ2', 'JZ3', 'JZ4'
    :param startTimestamp: 开始时间戳
    :param endTimestamp: 结束时间戳
    :param period: 统计周期，'daily'(按日), 'monthly'(按月), 'yearly'(按年)
    :return: 统计结果列表
    '''
    conn = sqlite3.connect('./data/hisdata.db')
    cursor = conn.cursor()
    # 修改: 使用一条SQL语句查询结果
    cursor.execute(
        '''SELECT "2",time FROM data WHERE name=? AND time BETWEEN ? AND ? ORDER BY time''',
        (jz_name, startTimestamp, endTimestamp))
    results = cursor.fetchall()
    conn.close()

    if period == 'daily':
        return _calculate_daily_hours(results)
    elif period == 'monthly':
        return _calculate_monthly_hours(results)
    elif period == 'yearly':
        return _calculate_yearly_hours(results)
    else:
        raise ValueError("period参数必须是 'daily', 'monthly' 或 'yearly' 之一")


def tongji_all_jz_hours(startTimestamp, endTimestamp, period='daily'):
    '''
    同时统计所有机组在时间区间内的运行小时数
    :param startTimestamp: 开始时间戳
    :param endTimestamp: 结束时间戳
    :param period: 统计周期，'daily'(按日), 'monthly'(按月), 'yearly'(按年)
    :return: 包含所有机组统计结果的字典
    '''
    conn = sqlite3.connect('./data/hisdata.db')
    cursor = conn.cursor()
    # 一次查询所有机组数据
    cursor.execute(
        '''SELECT name, "2", time FROM data WHERE name IN ('JZ1', 'JZ2', 'JZ3', 'JZ4') AND time BETWEEN ? AND ? ORDER BY name, time''',
        (startTimestamp, endTimestamp))
    results = cursor.fetchall()
    conn.close()

    # 按机组分组数据
    jz_data = {}
    for row in results:
        jz_name, value, timestamp = row
        if jz_name not in jz_data:
            jz_data[jz_name] = []
        jz_data[jz_name].append((value, timestamp))

    # 对每个机组计算统计结果
    jz_results = {}
    for jz_name, data in jz_data.items():
        if period == 'daily':
            jz_results[jz_name] = _calculate_daily_hours(data)
        elif period == 'monthly':
            jz_results[jz_name] = _calculate_monthly_hours(data)
        elif period == 'yearly':
            jz_results[jz_name] = _calculate_yearly_hours(data)

    return jz_results


def _calculate_daily_hours(results):
    '''
    按日计算运行小时数
    '''
    # 按日期分组统计运行小时数
    daily_data = {}

    for value, timestamp in results:
        # 将时间戳转换为日期
        date = datetime.fromtimestamp(timestamp).date()

        # 按日期分组存储数据
        if date not in daily_data:
            daily_data[date] = []
        daily_data[date].append((value, timestamp))

    result = []
    # 遍历每一天的数据
    for date, data_list in daily_data.items():
        # 检查是否全天数据点都非0（即运行状态）
        all_running = all(value != 0 for value, _ in data_list)
        if all_running:
            # 如果全天都在运行，返回24小时
            hours = 24
        else:
            # 如果包含0值，则计算实际运行时间
            hours = sum(10 / 60 for value, _ in data_list
                        if value != 0)  # 10分钟 = 1/6小时

        # 修改: 将timestamp转换为日期字符串
        date_str = date.strftime('%Y-%m-%d')
        # 计算运行天数（只要有运行就算1天）
        running_days = 1 if hours > 0 else 0
        result.append((round(hours, 2), running_days, date_str))

    return result


def _calculate_monthly_hours(results):
    '''
    按月计算运行小时数
    '''
    # 按月份分组统计运行小时数
    monthly_data = {}

    for value, timestamp in results:
        # 将时间戳转换为年月
        date = datetime.fromtimestamp(timestamp).date()
        month_key = date.strftime('%Y-%m')  # 年-月作为键

        # 按月份分组存储数据
        if month_key not in monthly_data:
            monthly_data[month_key] = []
        monthly_data[month_key].append((value, timestamp))

    result = []
    # 遍历每个月的数据
    for month, data_list in monthly_data.items():
        # 按日期分组以检查每天是否全天运行
        daily_data = {}
        for value, timestamp in data_list:
            date = datetime.fromtimestamp(timestamp).date()
            if date not in daily_data:
                daily_data[date] = []
            daily_data[date].append((value, timestamp))

        # 计算该月总运行小时数
        total_hours = 0
        running_days = 0
        for date, daily_list in daily_data.items():
            # 检查是否全天数据点都非0（即运行状态）
            all_running = all(value != 0 for value, _ in daily_list)

            if all_running:
                # 如果全天都在运行，返回24小时
                total_hours += 24
            else:
                # 如果包含0值，则计算实际运行时间
                day_hours = sum(10 / 60 for value, _ in daily_list
                                if value != 0)  # 10分钟 = 1/6小时
                total_hours += day_hours

            # 计算运行天数
            if any(value != 0 for value, _ in daily_list):
                running_days += 1

        result.append((total_hours, running_days, month))

    return result


def _calculate_yearly_hours(results):
    '''
    按年计算运行小时数
    '''
    # 按年份分组统计运行小时数
    yearly_data = {}

    for value, timestamp in results:
        # 将时间戳转换为年份
        date = datetime.fromtimestamp(timestamp).date()
        year_key = date.strftime('%Y')  # 年作为键

        # 按年份分组存储数据
        if year_key not in yearly_data:
            yearly_data[year_key] = []
        yearly_data[year_key].append((value, timestamp))

    result = []
    # 遍历每一年的数据
    for year, data_list in yearly_data.items():
        # 按日期分组以检查每天是否全天运行
        daily_data = {}
        for value, timestamp in data_list:
            date = datetime.fromtimestamp(timestamp).date()
            if date not in daily_data:
                daily_data[date] = []
            daily_data[date].append((value, timestamp))

        # 计算该年总运行小时数
        total_hours = 0
        running_days = 0
        for date, daily_list in daily_data.items():
            # 检查是否全天数据点都非0（即运行状态）
            all_running = all(value != 0 for value, _ in daily_list)

            if all_running:
                # 如果全天都在运行，返回24小时
                total_hours += 24
            else:
                # 如果包含0值，则计算实际运行时间
                day_hours = sum(10 / 60 for value, _ in daily_list
                                if value != 0)  # 10分钟 = 1/6小时
                total_hours += day_hours

            # 计算运行天数
            if any(value != 0 for value, _ in daily_list):
                running_days += 1

        result.append((total_hours, running_days, year))

    return result


# 为各个机组保留原有接口函数，但内部调用统一函数
def tongji_jz2_hours(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ2（机组2）运行的小时数，按日期分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'daily')
    return results.get('JZ2', [])


def tongji_jz2_hours_monthly(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ2（机组2）运行的小时数，按月分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'monthly')
    return results.get('JZ2', [])


def tongji_jz2_hours_yearly(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ2（机组2）运行的小时数，按年分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'yearly')
    return results.get('JZ2', [])


# 为JZ1机组添加统计函数
def tongji_jz1_hours(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ1（机组1）运行的小时数，按日期分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'daily')
    return results.get('JZ1', [])


def tongji_jz1_hours_monthly(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ1（机组1）运行的小时数，按月分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'monthly')
    return results.get('JZ1', [])


def tongji_jz1_hours_yearly(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ1（机组1）运行的小时数，按年分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'yearly')
    return results.get('JZ1', [])


# 为JZ3机组添加统计函数
def tongji_jz3_hours(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ3（机组3）运行的小时数，按日期分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'daily')
    return results.get('JZ3', [])


def tongji_jz3_hours_monthly(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ3（机组3）运行的小时数，按月分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'monthly')
    return results.get('JZ3', [])


def tongji_jz3_hours_yearly(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ3（机组3）运行的小时数，按年分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'yearly')
    return results.get('JZ3', [])


# 为JZ4机组添加统计函数
def tongji_jz4_hours(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ4（机组4）运行的小时数，按日期分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'daily')
    return results.get('JZ4', [])


def tongji_jz4_hours_monthly(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ4（机组4）运行的小时数，按月分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'monthly')
    return results.get('JZ4', [])


def tongji_jz4_hours_yearly(startTimestamp, endTimestamp):
    '''
    统计时间区间内JZ4（机组4）运行的小时数，按年分别累加非0运行时间
    '''
    results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'yearly')
    return results.get('JZ4', [])


def get_jz_tongji_daily(startTimestamp, endTimestamp):
    # 修改: 使用一次查询获取所有机组数据，提高效率
    daily_results = tongji_all_jz_hours(startTimestamp, endTimestamp, 'daily')
    # monthly_results = tongji_all_jz_hours(startTimestamp, endTimestamp,
    #                                       'monthly')
    # yearly_results = tongji_all_jz_hours(startTimestamp, endTimestamp,
    #                                      'yearly')
    return daily_results
    # return {
    #     "JZ1": [
    #         daily_results.get('JZ1', []),
    #         monthly_results.get('JZ1', []),
    #         yearly_results.get('JZ1', [])
    #     ],
    #     "JZ2": [
    #         daily_results.get('JZ2', []),
    #         monthly_results.get('JZ2', []),
    #         yearly_results.get('JZ2', [])
    #     ],
    #     "JZ3": [
    #         daily_results.get('JZ3', []),
    #         monthly_results.get('JZ3', []),
    #         yearly_results.get('JZ3', [])
    #     ],
    #     "JZ4": [
    #         daily_results.get('JZ4', []),
    #         monthly_results.get('JZ4', []),
    #         yearly_results.get('JZ4', [])
    #     ]
    # }


def get_watergrass(timepast=8):
    conn = sqlite3.connect(r'D:\myGitee\watergrassyolo\data\yolo.db')
    cursor = conn.cursor()
    cursor.execute(
        '''SELECT timestamp FROM watergrass ORDER BY timestamp DESC''')
    timestamp = cursor.fetchone()[0]
    timestamp = int(timestamp) - timepast * 3600
    cursor.execute(
        f'''SELECT wglarea,imgarea,timestamp,guessarea FROM watergrass WHERE timestamp >=? ORDER BY timestamp DESC ''',
        (timestamp, ))
    results = cursor.fetchall()
    conn.close()
    watergrass_data = []
    for wgarea, imgarea, timestamp, guessarea in results:
        date_str = datetime.fromtimestamp(timestamp).strftime(
            '%Y-%m-%d %H:%M:%S')
        watergrass_data.append([
            round(wgarea / imgarea * 100, 2), date_str,
            round(guessarea / imgarea * 100, 2) if guessarea else 0
        ])
    # print(watergrass_data)
    return watergrass_data


def save_plt(timepast=4):
    '''
    生成曲线图
    '''

    # 获取数据
    watergrass_data = get_watergrass(timepast)

    if not watergrass_data:
        return

    # 分离数据
    timestamps = []
    wglarea_ratios = []
    guessarea_ratios = []

    for item in watergrass_data:
        wglarea_ratio, date_str, guessarea_ratio = item
        # 转换日期字符串为datetime对象
        timestamp = datetime.strptime(date_str, '%Y-%m-%d %H:%M:%S')
        timestamps.append(timestamp)
        wglarea_ratios.append(wglarea_ratio)
        guessarea_ratios.append(guessarea_ratio)

    # 创建图表
    plt.figure(figsize=(12, 6))
    plt.plot(timestamps, wglarea_ratios, label='实际含量', marker='o')
    plt.plot(timestamps, guessarea_ratios, label='推测含量', marker='s')

    # 设置图表属性
    plt.xlabel('时间')
    plt.ylabel('含量百分比 (%)')
    plt.title(f'一站水草含量 (最近 {timepast} 小时)')
    plt.legend()
    plt.grid(True)

    # 格式化x轴时间显示
    plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%m-%d %H:%M'))
    plt.gca().xaxis.set_major_locator(mdates.HourLocator(interval=1))
    plt.gcf().autofmt_xdate()

    # 保存图表
    plt.savefig(f'watergrass_trend_{timepast}h.png',
                dpi=100,
                bbox_inches='tight')
    plt.close()


# print(get_jz_tongji(1756224000, 1756396799))
# print(tongji_all_jz_hours(1756224000, 1756396799, 'daily'))
if __name__ == "__main__":
    # print(get_watergrass())
    save_plt()
